package org.smile.excel.jdbc;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.smile.dataset.ArrayRow;
import org.smile.dataset.BaseDataSet;
import org.smile.dataset.DataSet;
import org.smile.dataset.DataSetMetaData;
import org.smile.dataset.DataSetMetaDataImpl;
import org.smile.io.IOUtils;
import org.smile.report.poi.PoiSupport;
import org.smile.util.SysUtils;

public class ExcelConnection implements Connection{
	/**连接的excel文件*/
	private File excelFile;
	
	private InputStream excelStream;
	/**当前工作表*/
	protected Workbook workbook;
	
	Map<String,DataSet> dataSets=new HashMap<String, DataSet>();
	
	protected ExcelConnection(File file){
		this.excelFile=file;
		try {
			this.excelStream=new BufferedInputStream(new FileInputStream(excelFile));
			this.workbook=PoiSupport.createWorkbook(excelStream);
		} catch (FileNotFoundException e) {
			SysUtils.log(e);
		}
	}
	/**
	 * 加载excel的sheet页
	 * @param sheetname
	 * @return
	 * @throws SQLException
	 */
	public DataSet loadDataSet(String sheetname) throws SQLException{
		DataSet dataset=dataSets.get(sheetname);
		if(dataset==null){
			//读取数据
			Sheet sheet =workbook.getSheet(sheetname);
			if(sheet==null){
				sheet=workbook.getSheetAt(0);
				if(!sheet.getSheetName().equals(sheetname)){
					throw new SQLException("can not find a table named "+sheetname);
				}
			}
			int lastRow=sheet.getLastRowNum();
			Row titleRow=sheet.getRow(0);
			short lastColNum=titleRow.getLastCellNum();
			short firstColNum=titleRow.getFirstCellNum();
			String[] titles=PoiSupport.getRowStringValues(titleRow,firstColNum , lastColNum);
			DataSetMetaData metaData=new DataSetMetaDataImpl(titles);
			dataset=new BaseDataSet(metaData);
			//开始读取数据
			for(int index=1;index<=lastRow;index++){
				Row dataRow=sheet.getRow(index);
				if(dataRow!=null){
					Object[] values=new Object[lastColNum-firstColNum];
					for(int j=firstColNum;j<lastColNum;j++){
						Object value=PoiSupport.getCellValue(dataRow.getCell(j));
						values[j]=value;
					}
					dataset.addRow(new ArrayRow(metaData, values));
				}else if(onReadNullRowData(index)){
					break;
				}
			}
			this.dataSets.put(sheetname, dataset);
		}
		return dataset;
		
	}
	
	/**
	 * 读取空行时
	 * 当返回true时会结束整个文件的读取 
	 * @return 是否结束整个文件的读取
	 */
	protected boolean onReadNullRowData(int rowIndex){
		return false;
	}

	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		return null;
	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return false;
	}

	@Override
	public Statement createStatement() throws SQLException {
		return new ExcelStatement(this);
	}

	@Override
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return new ExcelPreparedStatement(this, sql);
	}

	@Override
	public CallableStatement prepareCall(String sql) throws SQLException {
		return null;
	}

	@Override
	public String nativeSQL(String sql) throws SQLException {
		return null;
	}

	@Override
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		
	}

	@Override
	public boolean getAutoCommit() throws SQLException {
		return false;
	}

	@Override
	public void commit() throws SQLException {
		
	}

	@Override
	public void rollback() throws SQLException {
		
	}

	@Override
	public void close() throws SQLException {
		IOUtils.close(excelStream);
	}

	@Override
	public boolean isClosed() throws SQLException {
		return false;
	}

	@Override
	public DatabaseMetaData getMetaData() throws SQLException {
		return null;
	}

	@Override
	public void setReadOnly(boolean readOnly) throws SQLException {
		
	}

	@Override
	public boolean isReadOnly() throws SQLException {
		return false;
	}

	@Override
	public void setCatalog(String catalog) throws SQLException {
		
	}

	@Override
	public String getCatalog() throws SQLException {
		return null;
	}

	@Override
	public void setTransactionIsolation(int level) throws SQLException {
		
	}

	@Override
	public int getTransactionIsolation() throws SQLException {
		return 0;
	}

	@Override
	public SQLWarning getWarnings() throws SQLException {
		return null;
	}

	@Override
	public void clearWarnings() throws SQLException {
		
	}

	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
		return new ExcelStatement(this);
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		return new ExcelPreparedStatement(this, sql);
	}

	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		return null;
	}

	@Override
	public Map<String, Class<?>> getTypeMap() throws SQLException {
		return null;
	}

	@Override
	public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
		
	}

	@Override
	public void setHoldability(int holdability) throws SQLException {
		
	}

	@Override
	public int getHoldability() throws SQLException {
		return 0;
	}

	@Override
	public Savepoint setSavepoint() throws SQLException {
		return null;
	}

	@Override
	public Savepoint setSavepoint(String name) throws SQLException {
		return null;
	}

	@Override
	public void rollback(Savepoint savepoint) throws SQLException {
		
	}

	@Override
	public void releaseSavepoint(Savepoint savepoint) throws SQLException {
		
	}

	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
		return new ExcelStatement(this);
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
		return new ExcelPreparedStatement(this, sql);
	}

	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
		return new ExcelPreparedStatement(this, sql);
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
		return new ExcelPreparedStatement(this, sql);
	}

	@Override
	public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
		return new ExcelPreparedStatement(this, sql);
	}

	@Override
	public Clob createClob() throws SQLException {
		return null;
	}

	@Override
	public Blob createBlob() throws SQLException {
		return null;
	}

	@Override
	public NClob createNClob() throws SQLException {
		return null;
	}

	@Override
	public SQLXML createSQLXML() throws SQLException {
		return null;
	}

	@Override
	public boolean isValid(int timeout) throws SQLException {
		return false;
	}

	@Override
	public void setClientInfo(String name, String value) throws SQLClientInfoException {
		
	}

	@Override
	public void setClientInfo(Properties properties) throws SQLClientInfoException {
		
	}

	@Override
	public String getClientInfo(String name) throws SQLException {
		return null;
	}

	@Override
	public Properties getClientInfo() throws SQLException {
		return null;
	}

	@Override
	public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
		return null;
	}

	@Override
	public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
		return null;
	}

	@Override
	public void setSchema(String schema) throws SQLException {
		
	}

	@Override
	public String getSchema() throws SQLException {
		return this.excelFile.getName();
	}

	@Override
	public void abort(Executor executor) throws SQLException {
		
	}

	@Override
	public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
		
	}

	@Override
	public int getNetworkTimeout() throws SQLException {
		return 0;
	}

}
